package zhongqi.jdbc.dao;

import java.io.IOException;
import java.text.DecimalFormat;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mysql.jdbc.cacheutils.JdbcLruCache;

import lombok.extern.slf4j.Slf4j;



/**
 * @author  JYM
 * 2016-12-9 下午7:29:37
 */
@Slf4j
public class UserServlet extends HttpServlet{
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -6787930274613257652L;
	
	
     UserService userservice=new UserService();
     
     @Override
	 protected void doPost(HttpServletRequest req, HttpServletResponse resp)
	   throws ServletException, IOException {
	  doGet(req,resp);
	 }
     
     
     @Override
	 public void doGet(HttpServletRequest request, HttpServletResponse response)
	   throws ServletException, IOException {
    	 
         String method = request.getParameter("method");
    		if (method.equals("selectT10")) {
    			selectT10(request, response);
    		}else if(method.equals("selectT20")){
    			selectT20(request, response);
    		}else if(method.equals("selectT30")){
    			selectT30(request, response);
    		}else if(method.equals("selectT40")){
    			selectT40(request, response);
    		}else if (method.equals("select_with_where")) {
    			select_with_where(request, response);
    		}else if (method.equals("update")) {
    			update(request, response);
    		}else if (method.equals("delete")) {
    			delete(request, response);
    		}else if (method.equals("insert")) {
    			insert(request, response);
    		}else if(method.equals("state")){
    			getCacheState(request, response);
    		}
	 }
     
     //T10全表查询
	 private void selectT10(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		 log.info("获取10条数据！");
		 long startTime = System.nanoTime();
		 List<User> wxScoreInTrades=userservice.selectT10();
		 long endtTime = System.nanoTime();
		 long time=endtTime-startTime;
		 double c=(double) time/1000000.0;
		 DecimalFormat df = new DecimalFormat("0.00");//格式化小数   
		 String s = df.format(c);
		 request.setAttribute("time", s);
		 request.setAttribute("wxScoreInTrades", wxScoreInTrades);
		 request.getRequestDispatcher("page10.jsp").forward(request, response);
	     log.info("页面获取到数据！");
	}
	 
     //T20全表查询
	 private void selectT20(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		 log.info("获取10条数据！");
		 long startTime = System.nanoTime();
		 List<User> wxScoreInTrades=userservice.selectT20();
		 long endtTime = System.nanoTime();
		 long time=endtTime-startTime;
		 double c=(double) time/1000000.0;
		 DecimalFormat df = new DecimalFormat("0.00");//格式化小数   
		 String s = df.format(c);
		 request.setAttribute("time", s);
		 request.setAttribute("wxScoreInTrades", wxScoreInTrades);
		 request.getRequestDispatcher("page10.jsp").forward(request, response);
	     log.info("页面获取到数据！");
	}
	 
     //T30全表查询
	 private void selectT30(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		 log.info("获取10条数据！");
		 long startTime = System.nanoTime();
		 List<User> wxScoreInTrades=userservice.selectT30();
		 long endtTime = System.nanoTime();
		 long time=endtTime-startTime;
		 double c=(double) time/1000000.0;
		 DecimalFormat df = new DecimalFormat("0.00");//格式化小数   
		 String s = df.format(c);
		 request.setAttribute("time", s);
		 request.setAttribute("wxScoreInTrades", wxScoreInTrades);
		 request.getRequestDispatcher("page10.jsp").forward(request, response);
	     log.info("页面获取到数据！");
	}
	 
     //T40全表查询
	 private void selectT40(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		 log.info("获取10条数据！");
		 long startTime = System.nanoTime();
		 List<User> wxScoreInTrades=userservice.selectT40();
		 long endtTime = System.nanoTime();
		 long time=endtTime-startTime;
		 double c=(double) time/1000000.0;
		 DecimalFormat df = new DecimalFormat("0.00");//格式化小数   
		 String s = df.format(c);
		 request.setAttribute("time", s);
		 request.setAttribute("wxScoreInTrades", wxScoreInTrades);
		 request.getRequestDispatcher("page40.jsp").forward(request, response);
	     log.info("页面获取到数据！");
	}
	
	//缓存查询（带where条件）
	private void select_with_where(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		 log.info("获取100条数据！");
		  long startTime = System.nanoTime();
		  String user_sql=request.getParameter("sql");
		  if(user_sql==null){
			  user_sql = "select * from t10 where id=1";
		  }
		  List<User> wxScoreInTrades=userservice.select_with_where(user_sql);
		  long endtTime = System.nanoTime();
		  long time=endtTime-startTime;
		  double c=(double) time/1000000.0;
		  DecimalFormat df = new DecimalFormat("0.00");//格式化小数   
		  String s = df.format(c);
		request.setAttribute("time", s);
		request.setAttribute("wxScoreInTrades", wxScoreInTrades);
		request.setAttribute("sql", user_sql);
		request.getRequestDispatcher("page100.jsp").forward(request, response);
	    log.info("页面获取到数据！");
	}

	//增
	private void insert(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   	 String sql=request.getParameter("sql");
		 userservice.insert(sql);
		 getCacheState(request, response);
	}

	//删
	private void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   	 String sql=request.getParameter("sql");
		 userservice.delete(sql);
		 getCacheState(request, response);
	}
	
	//改
	private void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   	 String sql=request.getParameter("sql");
		 userservice.update(sql);
		 getCacheState(request, response);
	}
	
	//查询缓存状态
	private void getCacheState(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		log.info("获取缓存状态");
		List<String> tables = JdbcLruCache.getCacheTables();
		List<TableSqlList> sqlLists = JdbcLruCache.getCacheTablesSql();
		request.setAttribute("table1", tables.size()>0?tables.get(0):null);
		request.setAttribute("table2", tables.size()>1?tables.get(1):null);
		request.setAttribute("table3", tables.size()>2?tables.get(2):null);
		request.setAttribute("sqlLists", sqlLists);
		request.getRequestDispatcher("index.jsp").forward(request, response);
	}

}
